package DAO;

import java.sql.*;
import javax.swing.*;
import connect.ConnectDB;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class UserDAO {

    Connection conn;
    ResultSet rsUser = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;
    TreeMap<String, String> tmEmp;
    TreeMap<String, String> tmEmpIns;

    public UserDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataUser();
    }

    public void loadDataUser() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Account";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsUser = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Vector loadWellcome(String username) {
        String empId = "";
        String accLevel = "";
        String name = "";
        Vector vec = new Vector();
        try {
            rsUser.beforeFirst();
            while (rsUser.next()) {
                if (username.equalsIgnoreCase(rsUser.getString("USERNAME"))) {
                    empId = rsUser.getString("EMPID");
                    accLevel = rsUser.getString("ACCESS");
                }
            }

            String query = "SELECT "
                    + "             EMPNAME "
                    + "     FROM "
                    + "             Employees "
                    + "     WHERE "
                    + "             EMPID = ? ";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();
            rs.next();
            name = rs.getString(1);
            vec.add(name);  // Index 0
            vec.add(accLevel);  // Index 1
            return vec;
        } catch (Exception e) {
            System.out.println("Error in loadWellcome:" + e.getMessage());
            return null;
        }
    }

    public String checkUsername(String username) {
        try {
            loadDataUser();
            while (rsUser.next()) {
                if (rsUser.getString("USERNAME").equals(username)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            System.out.println("Error in checkusername: " + e.getMessage());
            return "Error";
        }
    }

    public Vector loadUserToUpdate(String username) {
        Vector<String> UserData = new Vector<String>();
        userLoadEmpId();
        String empId = "";

        try {
            rsUser.beforeFirst();
            while (rsUser.next()) {
                if (rsUser.getString("USERNAME").equals(username)) {
                    UserData.add(username);
                    UserData.add(rsUser.getString("PASSWORD"));

                    if (rsUser.getString("ACCESS").trim().equals("A")) {
                        UserData.add("Admin");
                    } else {
                        UserData.add("User");
                    }

                    empId = rsUser.getString("EMPID") + " - " + tmEmp.get(rsUser.getString("EMPID"));
                    UserData.add(empId);
                }
            }
            return UserData;
        } catch (Exception e) {
            System.out.println("Error in loadUserToUpdate: " + e.getMessage());
            return null;
        }
    }

    public void loadUserToInsert(JComboBox cbUserEmpId, JComboBox cbUserAccLevel) {
        try {
            Vector<String> vEmpId = new Vector<String>();
            Vector<String> vEmpName = new Vector<String>();
            String query = "select * from Employees as E LEFT OUTER JOIN Account as A ON A.EMPID = E.EMPID WHERE A.EMPID IS NULL";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vEmpId.add(rs.getString("EMPID"));
                vEmpName.add(rs.getString("EMPNAME").trim());
            }

            tmEmpIns = new TreeMap<String, String>();
            for (int i = 0; i < vEmpId.size(); i++) {
                tmEmpIns.put(vEmpId.get(i), vEmpName.get(i));
            }

            cbUserEmpId.removeAllItems();
            cbUserAccLevel.removeAllItems();

            cbUserEmpId.addItem("Please select...");
            cbUserAccLevel.addItem("Please select...");

            Set<String> keyEmp = tmEmpIns.keySet();
            for (String key : keyEmp) {
                cbUserEmpId.addItem(key + " - " + tmEmpIns.get(key));
            }

            cbUserAccLevel.addItem("Admin");
            cbUserAccLevel.addItem("User");

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in userLoadEmpId: " + e.getMessage());
        }
    }

    public boolean insertUser(Vector UserData) {
        try {
            String empId = getEmpId(UserData);

            rsUser.moveToInsertRow();

            rsUser.updateString(1, UserData.get(0).toString());
            rsUser.updateString(2, UserData.get(1).toString());
            rsUser.updateString(3, UserData.get(2).toString());
            rsUser.updateString(4, empId);

            rsUser.insertRow();
            loadDataUser();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertUser: " + e.getMessage());
            return false;
        }
    }

    public boolean updateUser(Vector UserData) {
        try {
            String empId = getEmpId(UserData);

            rsUser.beforeFirst();
            while (rsUser.next()) {
                if (rsUser.getString("username").equals(UserData.get(0))) {

                    //rsUser.updateString(1, UserData.get(0).toString());
                    rsUser.updateString(2, UserData.get(1).toString());
                    rsUser.updateString(3, UserData.get(2).toString());
                    rsUser.updateString(4, empId);

                    rsUser.updateRow();
                    loadDataUser();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in updateUser: " + e.getMessage());
            return false;
        }
    }

    public void userLoadEmpId() {
        try {
            Vector<String> vEmpId = new Vector<String>();
            Vector<String> vEmpName = new Vector<String>();
            //String query = "select * from Employees as E LEFT OUTER JOIN Account as A ON A.EMPID = E.EMPID WHERE A.EMPID IS NULL";
            String query = "SELECT * FROM Employees";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vEmpId.add(rs.getString("EMPID"));
                vEmpName.add(rs.getString("EMPNAME").trim());
            }

            tmEmp = new TreeMap<String, String>();
            for (int i = 0; i < vEmpId.size(); i++) {
                tmEmp.put(vEmpId.get(i), vEmpName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in userLoadEmpId: " + e.getMessage());
        }
    }

    public void loadUserCombobox(JComboBox cbUserEmpId, JComboBox cbUserAccLevel) {
        userLoadEmpId();

        cbUserEmpId.removeAllItems();
        cbUserAccLevel.removeAllItems();

        cbUserEmpId.addItem("Please select...");
        cbUserAccLevel.addItem("Please select...");

        Set<String> keyEmp = tmEmp.keySet();
        for (String key : keyEmp) {
            cbUserEmpId.addItem(key + " - " + tmEmp.get(key));
        }

        cbUserAccLevel.addItem("Admin");
        cbUserAccLevel.addItem("User");
    }

    public String getEmpId(Vector UserData) {
        String empId = (UserData.get(3).toString().split(" "))[0];
        return empId;
    }

    public DefaultTableModel loadTableUser() {
        try {
            loadDataUser();
            rsUser.beforeFirst();

            // Load column name
            ResultSetMetaData rsmd = rsUser.getMetaData();

            Vector colName = new Vector();

            colName.add("username");
            colName.add("Employee ID");
            colName.add("Access Level");

            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            // Load row data
            while (rsUser.next()) {
                Vector rowData = new Vector();
                rowData.add(rsUser.getString("USERNAME"));
                rowData.add(rsUser.getString("EMPID"));
                if (rsUser.getString("ACCESS").equals("U")) {
                    rowData.add("User");
                } else {
                    rowData.add("Admin");
                }
                dtm.addRow(rowData);
            }

            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean deleteUser(String username) {
        try {
            rsUser.beforeFirst();
            while (rsUser.next()) {
                if (rsUser.getString("username").trim().equals(username)) {
                    rsUser.deleteRow();
                    loadDataUser();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in deleteUser: " + e.getMessage());
            return false;
        }
    }

    public void closeConnect() {
        try {
            if (rsUser != null) {
                rsUser.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: User Account");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }

}
